using System;
using System.Data;
using System.Data.SqlClient;

namespace MessageBoard
{
	/// <summary>
	/// The Forum Groups
	/// </summary>
	public class UserGroup : Connection
	{
		private int _Id;
		private string _Title;
		private string _Description;

		public UserGroup()
		{
			_Title = "";
			_Description = "";
		}

		public static UserGroup Get(System.Data.DataRow dr)
		{
			UserGroup group = new UserGroup();
			group.Id = int.Parse(dr["Id"].ToString());
			group.Title = dr["Title"].ToString();
			group.Description = dr["Description"].ToString();
			return group;
		}

		public static UserGroup Get(int id)
		{
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter("SELECT * FROM " + Connection.tblUserGroup + " WHERE [Id] = @Id", DbConnection);
			getData.SelectCommand.Parameters.Add("@Id", id);
			getData.Fill(ds);
			if(ds.Tables[0].Rows.Count>0)
			{
				return Get(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}

		public static UserGroup Get(string groupCode)
		{
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter("SELECT * FROM " + Connection.tblUserGroup + " WHERE [Code] = @Code", DbConnection);
			getData.SelectCommand.Parameters.Add("@Code", groupCode);
			getData.Fill(ds);
			if(ds.Tables[0].Rows.Count>0)
			{
				return Get(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}

		public static System.Collections.ArrayList GetAll()
		{
			System.Collections.ArrayList arrayList = new System.Collections.ArrayList();
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter("SELECT * FROM " + Connection.tblUserGroup + "", DbConnection);
			getData.Fill(ds);
			foreach(System.Data.DataRow dr in ds.Tables[0].Rows)
			{
				arrayList.Add(Get(dr));
			}
			return arrayList;
		}

		public void Insert()
		{
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter(@"
			INSERT INTO " + Connection.tblUserGroup + @" 
			([Title], [Description], [Code])
			VALUES
			(@Title, @Description, @Code);
			SELECT @@identity as i", DbConnection);
			getData.SelectCommand.Parameters.Add("@Title", _Title);
			getData.SelectCommand.Parameters.Add("@Description", _Description);
			getData.SelectCommand.Parameters.Add("@Code", System.DBNull.Value);
			getData.Fill(ds);
			if(ds.Tables[0].Rows.Count>0)
			{
				_Id = int.Parse(ds.Tables[0].Rows[0]["i"].ToString());
			}
		}

		public void Update()
		{
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter(@"
			UPDATE " + Connection.tblUserGroup + @" SET 
			[Title] = @Title,
			[Description] = @Description
			WHERE [Id] = @Id", DbConnection);
			getData.SelectCommand.Parameters.Add("@Title", _Title);
			getData.SelectCommand.Parameters.Add("@Description", _Description);
			getData.SelectCommand.Parameters.Add("@Id", _Id);
			getData.Fill(ds);
		}

		public void Delete()
		{
			// Delete the related relations with users first.
			DeleteRelatedRelations();

			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter(@"
			DELETE FROM " + Connection.tblUserGroup + @" WHERE [Id] = @Id", DbConnection);
			getData.SelectCommand.Parameters.Add("@Id", _Id);
			getData.Fill(ds);
		}

		/// <summary>
		/// Delete the related categories.
		/// </summary>
		private void DeleteRelatedRelations()
		{
			foreach(UsersInGroups relation in UsersInGroups.GetAll(this))
			{
				relation.Delete();
			}
			
		}


		#region Properties
		
		/// <summary>
		/// The Id of the user group.
		/// </summary>
		public int Id
		{
			get
			{
				return _Id;
			}
			set
			{
				_Id = value;
			}
		}

		/// <summary>
		/// The Title of the user group, maximum length = 100
		/// </summary>
		public string Title
		{
			get
			{
				return _Title;
			}
			set
			{
				_Title = value;
			}
		}

		/// <summary>
		/// the Description of the group, maximum length = 255
		/// </summary>
		public string Description
		{
			get
			{
				return _Description;
			}
			set
			{
				_Description = value;
			}
		}

		

		#endregion
	}
}
